﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class CT_ChuyenBay_DAO
    {
        public static List<CT_ChuyenBay_DTO> layDanhSachChuyenBay()
        {
            return null;
        }
        public static List<CT_ChuyenBay_DTO> layChuyenBayTheoSanBayDi(string sbdi)
        {
            string query = "select lb.MaLB, cb.*" +
                     "from lichbay lb, (select cb1.*" +
                      "from chuyenbay cb1 , chitietsb ctsb, sanbay sb" +
                     "where cb1.Macb = ctsb.macb and ctsb.masb = sb.masb and  (sb.tinh = '" + sbdi + "' and ctsb.loai = 1) )cb" +
                        "where lb.MaCB = cb.macb ";


            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<CT_ChuyenBay_DTO> list = new List<CT_ChuyenBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                CT_ChuyenBay_DTO cb = new CT_ChuyenBay_DTO();

                //                MaCB varchar(9) not null,
                //SoGheHang1 int,
                //SoGheHang2 int,
                //GiaVeHang1 int,
                //GiaVeHang2 int,
                //GioDi datetime,
                //GioDen datetime,
                cb.maCB = dr[0].ToString();

                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.giaHang1 = int.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = int.Parse(dr["GiaVeHang2"].ToString());

                string malb = dr["MaLB"].ToString();

                LichBay_DTO lb = LichBay_DAO.layLichBayTheoMaLichBay(malb);
                cb.ngayDi = lb.ngayDi;

                SanBay_DTO sb = SanBay_DAO.laySanBayDenTheoMaCB(cb.maCB);

                cb.sbDi = sbdi;

                SanBay_DTO sbd = SanBay_DAO.laySanBayDenTheoMaCB(cb.maCB);
                cb.sbDen = sbd.tinh;

                cb.soGheDatHang1 = Ve_DAO.soGheDat1(malb);
                cb.soGheDatHang2 = Ve_DAO.soGheDat2(malb);
                cb.soGheHang1 = int.Parse(dr["soGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["soGheHang2"].ToString());
                cb.soGheMuaHang1 = Ve_DAO.soGheMuaHang1(malb);
                cb.soGheMuaHang2 = Ve_DAO.soGheMuaHang2(malb);
                cb.soGheTrongHang1 = Ve_DAO.soGheTrongHang1(malb);
                cb.soGheTrongHang2 = Ve_DAO.soGheTrongHang2(malb);
//thu thu thu      

                //cb.sanBayTrungGian = "hanoi";
                //cb.thoiGianDung = 56;

                list.Add(cb);
            }
            return list;
        }
        public static List<CT_ChuyenBay_DTO> layChuyenBayTheoSanBayDen(string sbDen)
        {
            string query = "select lb.MaLB, cb.*" +
                    "from lichbay lb, (select cb1.*" +
                     "from chuyenbay cb1 , chitietsb ctsb, sanbay sb" +
                    "where cb1.Macb = ctsb.macb and ctsb.masb = sb.masb and  (sb.tinh = '" + sbDen + "' and ctsb.loai = 2) )cb" +
                       "where lb.MaCB = cb.macb ";


            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<CT_ChuyenBay_DTO> list = new List<CT_ChuyenBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                CT_ChuyenBay_DTO cb = new CT_ChuyenBay_DTO();

                //                MaCB varchar(9) not null,
                //SoGheHang1 int,
                //SoGheHang2 int,
                //GiaVeHang1 int,
                //GiaVeHang2 int,
                //GioDi datetime,
                //GioDen datetime,
                cb.maCB = dr["MaCB"].ToString();

                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.giaHang1 = int.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = int.Parse(dr["GiaVeHang2"].ToString());

                string malb = dr["MaLB"].ToString();

                LichBay_DTO lb = LichBay_DAO.layLichBayTheoMaLichBay(malb);
                cb.ngayDi = lb.ngayDi;

                SanBay_DTO sbd = SanBay_DAO.laySanBayDiTheoMaCB(cb.maCB);
                cb.sbDi = sbd.tinh;


                cb.sbDen = sbDen;

                cb.soGheDatHang1 = Ve_DAO.soGheDat1(malb);
                cb.soGheDatHang2 = Ve_DAO.soGheDat2(malb);
                cb.soGheHang1 = int.Parse(dr["soGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["soGheHang2"].ToString());
                cb.soGheMuaHang1 = Ve_DAO.soGheMuaHang1(malb);
                cb.soGheMuaHang2 = Ve_DAO.soGheMuaHang2(malb);
                cb.soGheTrongHang1 = Ve_DAO.soGheTrongHang1(malb);
                cb.soGheTrongHang2 = Ve_DAO.soGheTrongHang2(malb);

                //thu thu thu      

                //cb.sanBayTrungGian = "hanoi";
                //cb.thoiGianDung = 56;
  
                list.Add(cb);
            }
            return list;
        }

        public static List<CT_ChuyenBay_DTO> LayChuyenBayTheoNgayDi(DateTime ngayDi)
        {
            string query = "select lb.MaLB, cb.*" +
                   "from lichbay lb, chuyenbay cb" +
                   "where lb.MaCB = cb.macb and lb.NgayDi = '" + ngayDi + "'";


            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<CT_ChuyenBay_DTO> list = new List<CT_ChuyenBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                CT_ChuyenBay_DTO cb = new CT_ChuyenBay_DTO();

                //                MaCB varchar(9) not null,
                //SoGheHang1 int,
                //SoGheHang2 int,
                //GiaVeHang1 int,
                //GiaVeHang2 int,
                //GioDi datetime,
                //GioDen datetime,
                cb.maCB = dr["MaCB"].ToString();

                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.giaHang1 = int.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = int.Parse(dr["GiaVeHang2"].ToString());

                string malb = dr["MaLB"].ToString();

                cb.ngayDi = ngayDi;

                SanBay_DTO sb = SanBay_DAO.laySanBayDenTheoMaCB(cb.maCB);
                cb.sbDen = sb.tinh;

                // cb.sbDi =
                SanBay_DTO sbd = SanBay_DAO.laySanBayDiTheoMaCB(cb.maCB);
                cb.sbDi = sbd.tinh;

                cb.soGheDatHang1 = Ve_DAO.soGheDat1(malb);
                cb.soGheDatHang2 = Ve_DAO.soGheDat2(malb);
                cb.soGheHang1 = int.Parse(dr["soGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["soGheHang2"].ToString());
                cb.soGheMuaHang1 = Ve_DAO.soGheMuaHang1(malb);
                cb.soGheMuaHang2 = Ve_DAO.soGheMuaHang2(malb);
                cb.soGheTrongHang1 = Ve_DAO.soGheTrongHang1(malb);
                cb.soGheTrongHang2 = Ve_DAO.soGheTrongHang2(malb);

                //thu thu thu      

                //cb.sanBayTrungGian = "hanoi";
                //cb.thoiGianDung = 56;

                list.Add(cb);
            }
            return list;
        }

        //public static List<CT_ChuyenBay_DTO> layChuyenBayTheoSanBayTGian(string sbtg)
        //{
        //    return null;
        //}
        public static List<CT_ChuyenBay_DTO> traCuuChuyenBay(string noiDi, string noiDen, DateTime ngayDi)
        {
            string query = "select lb.MaLB, cb.* " +
                    "from lichbay lb, (select cb1.* " +
                     "from chuyenbay cb1 , chitietsb ctsb, sanbay sb " +
                    "where cb1.Macb = ctsb.macb and ctsb.masb = sb.masb and ((sb.tinh = '" + noiDi + "' and ctsb.loai = 1) or (sb.tinh = '" + noiDen + "' and ctsb.loai = 2)) " +
                    "group by cb1.MaCB, cb1.giodi, cb1.gioden, cb1.soghehang1, cb1.soghehang2, cb1. giavehang1, cb1.giavehang2 " +
                    "having count(cb1.Macb) = 2	) cb " +
                    "where lb.MaCB = cb.macb and lb.NgayDi = '" + ngayDi + "'";


            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<CT_ChuyenBay_DTO> list = new List<CT_ChuyenBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                CT_ChuyenBay_DTO cb = new CT_ChuyenBay_DTO();

                //MaCB varchar(9) not null,
                //SoGheHang1 int,
                //SoGheHang2 int,
                //GiaVeHang1 int,
                //GiaVeHang2 int,
                //GioDi datetime,
                //GioDen datetime,
                cb.maCB = dr["MaCB"].ToString();

                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.giaHang1 = int.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = int.Parse(dr["GiaVeHang2"].ToString());

                string malb = dr["MaLB"].ToString();

                cb.ngayDi = ngayDi;


                cb.sbDen = noiDen;
                cb.sbDi = noiDi;

                cb.soGheDatHang1 = Ve_DAO.soGheDat1(malb);
                cb.soGheDatHang2 = Ve_DAO.soGheDat2(malb);
                cb.soGheHang1 = int.Parse(dr["soGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["soGheHang2"].ToString());
                cb.soGheMuaHang1 = Ve_DAO.soGheMuaHang1(malb);
                cb.soGheMuaHang2 = Ve_DAO.soGheMuaHang2(malb);
                cb.soGheTrongHang1 = Ve_DAO.soGheTrongHang1(malb);
                cb.soGheTrongHang2 = Ve_DAO.soGheTrongHang2(malb);

                //thu thu thu      

                //cb.sanBayTrungGian = "hanoi";
                //cb.thoiGianDung = 56;
          
                list.Add(cb);
            }
            return list;
        }
    }
}
